using Maticsoft.DBUtility;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace uTrade.Data
{
    public class RankDAL
    {
        public RankDAL()
        { }

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public static int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("Symbol", "tbl_Fund_RankInfo");
        }

        /// <summary>
        /// 清空
        /// </summary>
        public static int Clear()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_Fund_RankInfo ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public static bool Exists(string symbol)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbl_Fund_RankInfo");
            strSql.Append(" where Symbol=@symbol");
            SqlParameter[] parameters = {
                    new SqlParameter("@symbol", SqlDbType.VarChar ,10)
            };
            parameters[0].Value = symbol;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(Rank model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbl_Fund_RankInfo(");
            strSql.Append("Symbol, Name, Shorthand, CurDate, NetAssetValue, AccumulatedNet, DailyGrowth, LastWeek, LastMonth, Last3Month, Last6Month, LastYear, Last2Year, Last3Year, ThisYear, SinceBuilt, Custom, BuiltDate, Poundage, PoundageCount)");
            strSql.Append(" values (");
            strSql.Append("@Symbol, @Name, @Shorthand, @CurDate, @NetAssetValue, @AccumulatedNet, @DailyGrowth, @LastWeek, @LastMonth, @Last3Month, @Last6Month, @LastYear, @Last2Year, @Last3Year, @ThisYear, @SinceBuilt, @Custom, @BuiltDate, @Poundage, @PoundageCount)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@Symbol", SqlDbType.VarChar ,10),
new SqlParameter("@Name", SqlDbType.NVarChar ,50),
new SqlParameter("@Shorthand", SqlDbType.VarChar ,50),
new SqlParameter("@CurDate", SqlDbType.DateTime),
new SqlParameter("@NetAssetValue", SqlDbType.Decimal ,10),
new SqlParameter("@AccumulatedNet", SqlDbType.Decimal ,10),
new SqlParameter("@DailyGrowth", SqlDbType.Decimal ,10),
new SqlParameter("@LastWeek", SqlDbType.Decimal ,10),
new SqlParameter("@LastMonth", SqlDbType.Decimal ,10),
new SqlParameter("@Last3Month", SqlDbType.Decimal ,10),
new SqlParameter("@Last6Month", SqlDbType.Decimal ,10),
new SqlParameter("@LastYear", SqlDbType.Decimal ,10),
new SqlParameter("@Last2Year", SqlDbType.Decimal ,10),
new SqlParameter("@Last3Year", SqlDbType.Decimal ,10),
new SqlParameter("@ThisYear", SqlDbType.Decimal ,10),
new SqlParameter("@SinceBuilt", SqlDbType.Decimal ,10),
new SqlParameter("@Custom", SqlDbType.VarChar ,20),
new SqlParameter("@BuiltDate", SqlDbType.VarChar ,20),
new SqlParameter("@Poundage", SqlDbType.VarChar ,20),
new SqlParameter("@PoundageCount", SqlDbType.VarChar ,20)};
            parameters[0].Value = model.Symbol;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Shorthand;
            parameters[3].Value = model.CurDate;
            parameters[4].Value = model.NetAssetValue;
            parameters[5].Value = model.AccumulatedNet;
            parameters[6].Value = model.DailyGrowth;
            parameters[7].Value = model.LastWeek;
            parameters[8].Value = model.LastMonth;
            parameters[9].Value = model.Last3Month;
            parameters[10].Value = model.Last6Month;
            parameters[11].Value = model.LastYear;
            parameters[12].Value = model.Last2Year;
            parameters[13].Value = model.Last3Year;
            parameters[14].Value = model.ThisYear;
            parameters[15].Value = model.SinceBuilt;
            parameters[16].Value = model.Custom;
            parameters[17].Value = model.BuiltDate;
            parameters[18].Value = model.Poundage;
            parameters[19].Value = model.PoundageCount;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static int Update(Rank model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbl_Fund_RankInfo set ");
            strSql.Append("Name = @Name,");
            strSql.Append("Shorthand = @Shorthand,");
            strSql.Append("CurDate = @CurDate,");
            strSql.Append("NetAssetValue = @NetAssetValue,");
            strSql.Append("AccumulatedNet = @AccumulatedNet,");
            strSql.Append("DailyGrowth = @DailyGrowth,");
            strSql.Append("LastWeek = @LastWeek,");
            strSql.Append("LastMonth = @LastMonth,");
            strSql.Append("Last3Month = @Last3Month,");
            strSql.Append("Last6Month = @Last6Month,");
            strSql.Append("LastYear = @LastYear,");
            strSql.Append("Last2Year = @Last2Year,");
            strSql.Append("Last3Year = @Last3Year,");
            strSql.Append("ThisYear = @ThisYear,");
            strSql.Append("SinceBuilt = @SinceBuilt,");
            strSql.Append("Custom = @Custom,");
            strSql.Append("BuiltDate = @BuiltDate,");
            strSql.Append("Poundage = @Poundage,");
            strSql.Append("PoundageCount = @PoundageCount,");

            strSql.Append(" where Symbol=@symbol");
            SqlParameter[] parameters = {
                    new SqlParameter("@Symbol", SqlDbType.VarChar ,10),
new SqlParameter("@Name", SqlDbType.NVarChar ,50),
new SqlParameter("@Shorthand", SqlDbType.VarChar ,50),
new SqlParameter("@CurDate", SqlDbType.DateTime),
new SqlParameter("@NetAssetValue", SqlDbType.Decimal ,10),
new SqlParameter("@AccumulatedNet", SqlDbType.Decimal ,10),
new SqlParameter("@DailyGrowth", SqlDbType.Decimal ,10),
new SqlParameter("@LastWeek", SqlDbType.Decimal ,10),
new SqlParameter("@LastMonth", SqlDbType.Decimal ,10),
new SqlParameter("@Last3Month", SqlDbType.Decimal ,10),
new SqlParameter("@Last6Month", SqlDbType.Decimal ,10),
new SqlParameter("@LastYear", SqlDbType.Decimal ,10),
new SqlParameter("@Last2Year", SqlDbType.Decimal ,10),
new SqlParameter("@Last3Year", SqlDbType.Decimal ,10),
new SqlParameter("@ThisYear", SqlDbType.Decimal ,10),
new SqlParameter("@SinceBuilt", SqlDbType.Decimal ,10),
new SqlParameter("@Custom", SqlDbType.VarChar ,20),
new SqlParameter("@BuiltDate", SqlDbType.VarChar ,20),
new SqlParameter("@Poundage", SqlDbType.VarChar ,20),
new SqlParameter("@PoundageCount", SqlDbType.VarChar ,20)};
            parameters[0].Value = model.Symbol;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Shorthand;
            parameters[3].Value = model.CurDate;
            parameters[4].Value = model.NetAssetValue;
            parameters[5].Value = model.AccumulatedNet;
            parameters[6].Value = model.DailyGrowth;
            parameters[7].Value = model.LastWeek;
            parameters[8].Value = model.LastMonth;
            parameters[9].Value = model.Last3Month;
            parameters[10].Value = model.Last6Month;
            parameters[11].Value = model.LastYear;
            parameters[12].Value = model.Last2Year;
            parameters[13].Value = model.Last3Year;
            parameters[14].Value = model.ThisYear;
            parameters[15].Value = model.SinceBuilt;
            parameters[16].Value = model.Custom;
            parameters[17].Value = model.BuiltDate;
            parameters[18].Value = model.Poundage;
            parameters[19].Value = model.PoundageCount;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public static bool Delete(string symbol)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_Fund_RankInfo ");
            strSql.Append(" where Symbol=@symbol");
            SqlParameter[] parameters = {
                    new SqlParameter("@symbol", SqlDbType.VarChar ,10)
            };
            parameters[0].Value = symbol;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        public static int DeleteList(string symbollist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_Fund_RankInfo ");
            strSql.Append(" where Symbol in (" + symbollist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Rank GetModel(string symbol)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Symbol, Name, Shorthand, CurDate, NetAssetValue, AccumulatedNet, DailyGrowth, LastWeek, LastMonth, Last3Month, Last6Month, LastYear, Last2Year, Last3Year, ThisYear, SinceBuilt, Custom, BuiltDate, Poundage, PoundageCount from tbl_Fund_RankInfo ");
            strSql.Append(" where Symbol=@symbol");
            SqlParameter[] parameters = {
                    new SqlParameter("@symbol", SqlDbType.VarChar ,10)
            };
            parameters[0].Value = symbol;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ModelConvertHelper<Rank>.ConvertToModel(ds.Tables[0])?[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Symbol, Name, Shorthand, CurDate, NetAssetValue, AccumulatedNet, DailyGrowth, LastWeek, LastMonth, Last3Month, Last6Month, LastYear, Last2Year, Last3Year, ThisYear, SinceBuilt, Custom, BuiltDate, Poundage, PoundageCount ");
            strSql.Append(" FROM tbl_Fund_RankInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public static DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" Symbol, Name, Shorthand, CurDate, NetAssetValue, AccumulatedNet, DailyGrowth, LastWeek, LastMonth, Last3Month, Last6Month, LastYear, Last2Year, Last3Year, ThisYear, SinceBuilt, Custom, BuiltDate, Poundage, PoundageCount ");
            strSql.Append(" FROM tbl_Fund_RankInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public static int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tbl_Fund_RankInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.Symbol desc");
            }
            strSql.Append(")AS Row, T.*  from tbl_Fund_RankInfo T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetFavoriateList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");

            strSql.Append(" fav.Symbol,Name,Shorthand,CurDate,NetAssetValue,AccumulatedNet,DailyGrowth,LastWeek,LastMonth,Last3Month,Last6Month,LastYear,Last2Year,Last3Year,ThisYear,SinceBuilt,BuiltDate,Custom,Poundage,PoundageCount,Favorite ");
            strSql.Append(" from tbl_Favoriate fav left join tbl_fund_RankInfo rank on (fav.Symbol = rank.Symbol)");
            strSql.Append(" where fav.Type = " + ((int)TradeType.Fund).ToString());

            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "tbl_Fund_RankInfo";
            parameters[1].Value = "id";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/
    }
}